
// const {connection} =require('./sqlhelper');
const mysql=require('mysql');
const connection = mysql.createConnection({
    host:'127.0.0.1',
    user:'root',
    password:'root',
    database:'albx_38'
})
module.exports={
    getAllPosts(callback) {
        let sql = `SELECT
    posts.id,posts.title,posts.created,posts.\`status\`,
    categories.\`name\`,
    users.nickname
   FROM posts
  LEFT JOIN categories ON posts.category_id = categories.id
  LEFT JOIN users ON posts.user_id = users.id`
        connection.query(sql,(err,result)=>{
            err&&console.log(err);
            callback(result)
        })
    },
    getPostsByPage(pageIndex, pageSize,callback){
        // console.log(pageIndex, pageSize);
        
        let sql = `SELECT
    posts.id,posts.title,posts.created,posts.\`status\`,
    categories.\`name\`,
    users.nickname
   FROM posts
  LEFT JOIN categories ON posts.category_id = categories.id
  LEFT JOIN users ON posts.user_id = users.id
  LIMIT ${(pageIndex - 1) * pageSize},${pageSize}`;
        connection.query(sql,(err,result)=>{
            err && console.log(err);
            callback(result)
        })
    },
    // 获取posts的总条数是多少
    getPostCount(callback){
        let sql = `SELECT count(id) as total FROM posts`;
         connection.query(sql, (err, result) => {
             err && console.log(err);
             callback(result[0])
         })
    },
    // 根据条件的筛选的时候获取总条数
    getPostCountByFilter(condition, callback) {
        let sql = ` SELECT count(id) as total FROM posts`;
        if (condition!=undefined){
            sql += condition
        }
        connection.query(sql, (err, result) => {
            err && console.log(err);
            callback(result[0])
        })
    },
    getPostsByFilter(sql, callback) {
        connection.query(sql, (err, result) => {
            err && console.log(err);
            callback(result)
        })
    },
    deletePostsById(id,callback){
        let sql = `DELETE FROM posts WHERE id='${id}'`;
        connection.query(sql, (err, result) => {
            err && console.log(err);
            callback(result)
        })
    },
    deletePostByMutiple(ids,callback){
        let idsstr=ids.join();
        // console.log(idsstr);
        let sql = `DELETE FROM posts WHERE id in (${idsstr})`;
        connection.query(sql,(err,result)=>{
            err && console.log(err);
            callback(result)
        })
    },
      // 前台主页获取最新发布的文章
    getLastedPosts(callback){
        let sql = ` SELECT posts.id,posts.title,posts.created,posts.content,posts.views,posts.likes,posts.feature,
 users.nickname,categories.\`name\`,
 (SELECT COUNT(id) FROM comments WHERE post_id=posts.id) AS commentToal
 FROM posts
 LEFT JOIN users ON posts.user_id=users.id
 LEFT JOIN categories on posts.category_id=categories.id
 ORDER BY posts.created DESC
 LIMIT 0,5`;
        connection.query(sql,(err,result)=>{
             err && console.log(err);
             callback(result)
        })
    },
    // list页面根据id获取数据
    getPostsByCategoryId(id, pageIndex, callback) {
        let sql = `SELECT
    posts.id,posts.title,posts.created,posts.content,posts.views,posts.likes,posts.feature,
    users.nickname,categories.\`name\`,
    (SELECT count(id) FROM comments WHERE post_id = posts.id) as commentsTotal
  FROM posts
  LEFT JOIN categories ON posts.category_id = categories.id
  LEFT JOIN users ON posts.user_id = users.id
WHERE posts.category_id=${id}
  ORDER BY posts.created DESC
  LIMIT ${(pageIndex-1)*5},5`;
        connection.query(sql, (err, result) => {
            err && console.log(err);
            callback(result)
        })
    }

}